package com.global.service.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.CannotGetJdbcConnectionException;
import org.springframework.stereotype.Repository;

import com.global.base.ExceptionUtil;
import com.global.base.StringUtil;
import com.global.dao.DataSourceXColumnDao;
import com.global.dao.DataSourceXDao;
import com.global.dao.QueryParameterDefinitionDao;
import com.global.dao.jdbc.GenericJdbcDao;
import com.global.model.DataSourceX;
import com.global.model.Grid;
import com.global.model.QueryParameterDefinition;
import com.global.query.ParsedSqlQuery;
import com.global.query.ResultSetOutput;
import com.global.query.NamedParameterQuery;
import com.global.query.StaticQuery;
import com.global.service.AbstractService;
import com.global.service.ReportInstanceService;
import com.global.vo.DBColumn;
import com.global.vo.QueryParameter;
import com.global.vo.QueryType;
import com.global.vo.RowValuesProperties;

@Repository("reportInstanceService")
public class ReportInstanceServiceImpl extends AbstractService implements ReportInstanceService {

	@Autowired
	DataSourceXDao dataSourceXDao;
	@Autowired
	DataSourceXColumnDao dataSourceXColumnDao;
	@Autowired
	QueryParameterDefinitionDao queryParameterDfDao;
	
	@Autowired
	GenericJdbcDao jdbcDao;
	
	List<String> errorMessageList = new ArrayList<String>();
	
	public String getErrorMessage() {
		return StringUtil.getString(errorMessageList, ",");
	}
	
	/**
	 * {@inheritDoc}
	 */
	public List<RowValuesProperties> generateData(Grid grid, List<QueryParameter> queryParameters){
		if(grid!=null){
			DataSourceX dataSourceX = grid.getDataSourceX();
			String queryType = dataSourceX.getQueryType();
			
			if(queryType.equalsIgnoreCase(QueryType.QueryType_StaticSQL)){
				if(queryParameters==null || queryParameters.isEmpty()){
					return generateDataForStaticSql(dataSourceX);
				}else{
					return generateDataForNamedParameterSql(dataSourceX, queryParameters);
				}
			}else if(queryType.equalsIgnoreCase(QueryType.QueryType_NamedParameterSQL)){
				if(queryParameters==null || queryParameters.isEmpty()){					
					errorMessageList.add("没有传入参数值，则无法查询");
				}else{
					return generateDataForNamedParameterSql(dataSourceX, queryParameters);
				}
			}else if(queryType.equalsIgnoreCase(QueryType.QueryType_DynamicSQL)){
				
			}
		}
		
		return new ArrayList<RowValuesProperties>();
	}
	
	private List<RowValuesProperties> generateDataForStaticSql(DataSourceX dataSourceX){
		String errMsg = "";
		
		if(dataSourceX!=null){
			if(QueryType.QueryType_StaticSQL.equalsIgnoreCase(dataSourceX.getQueryType())){
				String sql = dataSourceX.getSql();
				StaticQuery staticQuery = new StaticQuery(sql);
				List<DBColumn> sqlMetaData = jdbcDao.getStaticSQLMetaData(staticQuery.getParseSql());
				if(sqlMetaData.isEmpty()){
					errMsg = "数据源【" + dataSourceX.getDescription() + "】绑定的SQL存在问题【" + jdbcDao.getErrorMessage() + "】，则无法查询数据";
					errorMessageList.add(errMsg);
					log.error(errMsg);
					
					return new ArrayList<RowValuesProperties>();
				}else{
					try {
						ResultSet rs = jdbcDao.getResultSet(sql);
						ResultSetOutput resultSetOutput = new ResultSetOutput(dataSourceX, rs);
						return resultSetOutput.getRowData();
					} catch (CannotGetJdbcConnectionException e) {
						errorMessageList.add(ExceptionUtil.getString(e));
					} catch (SQLException e) {
						errorMessageList.add(ExceptionUtil.getString(e));
					}
					
					return new ArrayList<RowValuesProperties>();
				}
			}else{
				errMsg = "数据源【" + dataSourceX.getDescription() + "】的数据源类型不是静态SQL，则无法按照静态SQL方式查询数据";
				errorMessageList.add(errMsg);
				
				return new ArrayList<RowValuesProperties>();
			}
		}else{
			errorMessageList.add("数据源为空，则无法查询数据");
			
			return new ArrayList<RowValuesProperties>();
		}
	}	
	
	/**
	 * 将参数（包括固定参数以及过滤数据参数）传入到带命名参数的SQL中，获取数据
	 * @param dataSourceX
	 * @param queryParameters
	 */
	private List<RowValuesProperties> generateDataForNamedParameterSql(DataSourceX dataSourceX, List<QueryParameter> queryParameters){
		String errMsg = "";
		
		if(dataSourceX!=null){
			//获取带命名参数的select语句
			ParsedSqlQuery parseSqlStatement = NamedParameterQuery.parseSqlStatement(dataSourceX.getSql());
			//获取解析后的SQL的固定参数清单
			List<String> fixedParameterNames = parseSqlStatement.getParameterNames();
			//获取固定参数定义清单
			List<QueryParameterDefinition> fixedParameterDefinitions = dataSourceX.getFixedParameterDefinitions();
			//获取过滤数据参数定义清单
			List<QueryParameterDefinition> filterParameterDefinitions = dataSourceX.getFilterParameterDefinitions();

			if(fixedParameterNames.size()!=fixedParameterDefinitions.size()){
				errMsg = "数据源【" + dataSourceX.getDescription() + "】绑定的SQL已经发生变化与配置好的命名参数不一致，则无法查询数据";
				errorMessageList.add(errMsg);
				log.error(errMsg);
				
				return new ArrayList<RowValuesProperties>();
			}				

			//统计固定参数值清单
			List<QueryParameter> namedParameters = new ArrayList<QueryParameter>(fixedParameterDefinitions.size());
			for(QueryParameterDefinition queryParameterDf : fixedParameterDefinitions){
				for(QueryParameter queryParameter : queryParameters){
					if(queryParameterDf.getName().equalsIgnoreCase(queryParameter.getName())){
						queryParameter.setName(queryParameterDf.getName());
						namedParameters.add(queryParameter);
						break;
					}
				}
			}
			//统计过滤数据参数值清单
			List<QueryParameter> filterParameters = new ArrayList<QueryParameter>(filterParameterDefinitions.size());
			for(QueryParameterDefinition queryParameterDf : filterParameterDefinitions){
				for(QueryParameter queryParameter : queryParameters){
					if(queryParameterDf.getName().equalsIgnoreCase(queryParameter.getName())){
						queryParameter.setName(queryParameterDf.getName());
						filterParameters.add(queryParameter);
						break;
					}
				}
			}
			
			//获取元数据
			List<DBColumn> sqlMetaData = jdbcDao.getNamedParameterSQLMetaData(parseSqlStatement.getOriginalSql(), namedParameters);
			if(sqlMetaData.isEmpty()){
				errMsg = "数据源【" + dataSourceX.getDescription() + "】绑定的SQL存在问题【" + jdbcDao.getErrorMessage() + "】，则无法查询数据";
				errorMessageList.add(errMsg);
				log.error(errMsg);
				
				return new ArrayList<RowValuesProperties>();
			}else{
				try {
					//String originalSql = parseSqlStatement.getOriginalSql();
					StringBuffer originalSql = new StringBuffer("Select * From (");
					originalSql.append(parseSqlStatement.getOriginalSql());
					originalSql.append(")");
					if(!filterParameters.isEmpty()){
						originalSql.append(" Where ");
						
						for(QueryParameter q : filterParameters){
							if(!q.isExistError()){
								if(q.isQueryEmpty()){
									originalSql.append(" And " + q.getName() + " is Null ");
								}else if(q.isQueryNotEmpty()){
									originalSql.append(" And " + q.getName() + " is Not Null ");
								}else{
									originalSql.append(" And " + q.getName() + " = '" + NamedParameterQuery.getNamedParameterValueStr(q.getValue() + "'"));
								}
							}							
						}
					}
					
					log.debug(originalSql.toString());
					log.debug(queryParameters.toString());
					ResultSet rs = jdbcDao.getResultSet(originalSql.toString(), queryParameters);
					ResultSetOutput resultSetOutput = new ResultSetOutput(dataSourceX, rs);
					return resultSetOutput.getRowData();
				} catch (CannotGetJdbcConnectionException e) {
					errorMessageList.add(ExceptionUtil.getString(e));
				} catch (SQLException e) {
					errorMessageList.add(ExceptionUtil.getString(e));
				}
				
				return new ArrayList<RowValuesProperties>();
			}
		}else{
			errorMessageList.add("数据源为空，则无法查询数据");
			
			return new ArrayList<RowValuesProperties>();
		}
	}
}
